﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OPENXMLLib
{
    public static class ValidationResolver
    {
        /// <summary>
        /// 按照错误的公式信息删除
        /// child.Formula1.Text.StartsWith(errorFormulaPrefix)
        /// </summary>
        /// <param name="xDoc"></param>
        /// <param name="sheetName"></param>
        /// <param name="errorFormulaPrefix"></param>
        public static string ClearAll(SpreadsheetDocument xDoc, string sheetName, string errorFormulaPrefix)
        {
            StringBuilder strMessage = new StringBuilder();

            IEnumerable<Sheet> sheets = xDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName).ToList();
            if (sheets.Count() == 0)
            {
                //找出合适前提的sheet,没有则返回
                throw new Exception("未发现Sheet");
            }
            Sheet sheet = sheets.First();
            WorksheetPart worksheetPart = (WorksheetPart)xDoc.WorkbookPart.GetPartById(sheets.First().Id);

            SheetData data = worksheetPart.Worksheet.Elements<SheetData>().ToList().FirstOrDefault();

            List<Row> rows = data.Elements<Row>().ToList();

            DataValidations dataValidations = worksheetPart.Worksheet.Elements<DataValidations>().FirstOrDefault();

            List<DataValidation> children = dataValidations.Elements<DataValidation>().ToList();

            for (int index = 0; index < children.Count;)
            {
                DataValidation child = children[index];
                if (child.Formula1 != null && child.Formula1.Text.ToUpper().StartsWith(errorFormulaPrefix.ToUpper()))
                {
                    strMessage.AppendFormat("删除数据有效性:{0}", child.Formula1.Text);
                    strMessage.AppendLine();
                    child.Remove();
                }
                else
                {
                    index++;
                }
            }

            worksheetPart.Worksheet.Save();

            return strMessage.ToString();
        }


        /// <summary>
        /// 去除excel中的扩展标记，多数是由于经过了多个版本的excel处理之后遗留下来的信息
        /// 
        /// </summary>
        /// <param name="xDoc"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static string RemoveExtList(SpreadsheetDocument xDoc, string sheetName)
        {
            // 应该只需要删除ns和文档的ns不一致的节点，不过目前是全部删除
            /* 
    <extLst>
        <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
            <x14:dataValidations count="6" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
                <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>INDIRECT('02-Metadata'!#REF!)</xm:f>
                    </x14:formula1>
                    <xm:sqref>N12</xm:sqref>
                </x14:dataValidation>
                <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>INDIRECT('02-Metadata'!#REF!)</xm:f>
                    </x14:formula1>
                    <xm:sqref>M12</xm:sqref>
                </x14:dataValidation>
                <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>INDIRECT('02-Metadata'!#REF!)</xm:f>
                    </x14:formula1>
                    <xm:sqref>V12</xm:sqref>
                </x14:dataValidation>
                <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>INDIRECT('02-Metadata'!$G$7)</xm:f>
                    </x14:formula1>
                    <xm:sqref>M13:M16</xm:sqref>
                </x14:dataValidation>
                <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>INDIRECT('02-Metadata'!$G$8)</xm:f>
                    </x14:formula1>
                    <xm:sqref>N13:N16</xm:sqref>
                </x14:dataValidation>
                <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>INDIRECT('02-Metadata'!$G$9)</xm:f>
                    </x14:formula1>
                    <xm:sqref>V13:V16</xm:sqref>
                </x14:dataValidation>
            </x14:dataValidations>
        </ext>
    </extLst>
             */
            StringBuilder strMessage = new StringBuilder();

            IEnumerable<Sheet> sheets = xDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName).ToList();
            if (sheets.Count() == 0)
            {
                //找出合适前提的sheet,没有则返回
                throw new Exception("未发现Sheet");
            }
            Sheet sheet = sheets.First();
            WorksheetPart worksheetPart = (WorksheetPart)xDoc.WorkbookPart.GetPartById(sheets.First().Id);
            WorksheetExtensionList extList = worksheetPart.Worksheet.Elements<WorksheetExtensionList>().FirstOrDefault();

            if (extList != null)
            {
                extList.Remove();
                worksheetPart.Worksheet.Save();

                strMessage.Append("处理成功!");
            }
            else
            {
                strMessage.Append("没有发现扩展信息!");
            }

            return strMessage.ToString();
        }

        public static void Resolve(SpreadsheetDocument xDoc)
        {
            IEnumerable<Sheet> sheets = xDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().ToList();
            if (sheets.Count() == 0)
            {
                //找出合适前提的sheet,没有则返回
                throw new Exception("未发现Sheet");
            }

            foreach (var sheet in sheets)
            {
                WorksheetPart worksheetPart = (WorksheetPart)xDoc.WorkbookPart.GetPartById(sheet.Id);
                WorksheetExtensionList extList = worksheetPart.Worksheet.Elements<WorksheetExtensionList>().FirstOrDefault();

                if (extList != null && extList.ChildElements.Count > 0)
                {
                    extList.Remove();

                    worksheetPart.Worksheet.Save();

                    System.Diagnostics.Debug.WriteLine(string.Format("处理{0}中的WorksheetExtensionList", sheet.Name));
                }
                else
                {
                    System.Diagnostics.Debug.WriteLine(string.Format("{0}中没有发现WorksheetExtensionList", sheet.Name));
                }

                //// 去除2010以上版本的命名空间信息
                //List<string> needRemoveNSKeys = new List<string>();
                //string mcNsUrl = string.Empty;
                //foreach (var item in worksheetPart.Worksheet.NamespaceDeclarations)
                //{
                //    if (item.Key.ToUpper() == "MC")
                //    {
                //        mcNsUrl = item.Value;
                //    }

                //    if (item.Value == "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac")
                //    {
                //        needRemoveNSKeys.Add(item.Key);
                //    }
                //}

                //if (needRemoveNSKeys.Count > 0)
                //{
                //    foreach (var nskey in needRemoveNSKeys)
                //    {
                //        System.Diagnostics.Debug.WriteLine(string.Format("从{0}中移除NameSpace:{1},{2}", sheet.Name, nskey, sheet.NamespaceDeclarations.Where(p => p.Key == nskey).Select(p => p.Value).SingleOrDefault()));
                //        worksheetPart.Worksheet.RemoveNamespaceDeclaration(nskey);
                //    }

                //    //mc:Ignorable="x14ac"去掉
                //    if (!string.IsNullOrEmpty(mcNsUrl))
                //    {
                //        worksheetPart.Worksheet.RemoveAttribute("Ignorable", mcNsUrl);
                //    }

                //    worksheetPart.Worksheet.Save();
                //}
            }
        }
    }
}
